package com.jx.lcs.integral.dao;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import com.jx.lcs.integral.entity.UserIntegral;
import com.tianyu.jty.common.persistence.HibernateDao;
import com.tianyu.jty.common.persistence.Page;
import com.tianyu.jty.common.utils.DateUtils;

/**
 * 用户积分DAO
 * @author JiaYY
 * @date 2015年8月25日
 */
@Repository
public class UserIntegralDao extends HibernateDao<UserIntegral, String> {

	/**
	 * 获取用户积分列表
	 */
	@SuppressWarnings("unchecked")
	public List<UserIntegral> findUserIntegralList(Page<UserIntegral> page, HttpServletRequest request) {
		List<UserIntegral> result = new ArrayList<UserIntegral>();
		try{
			String userName = request.getParameter("userName");
			String uid = request.getParameter("uid");
			String telephone = request.getParameter("telephone");
			String beginDate = request.getParameter("beginDate");
			String endDate = request.getParameter("endDate");
			if(!StringUtils.isBlank(endDate)) endDate = DateUtils.addDays(endDate, 1);
			
			StringBuffer sb = new StringBuffer();
			sb.append("select t4.uid, t4.userName, t4.telephone, t4.userDept, t4.city, t4.postLevel, t4.headImg, t3.incomeScore, t3.outputScore from (select t1.uid, t1.incomeScore, ifnull(t2.outputScore, 0) outputScore from (select t.uid, sum(t.score) incomeScore from tb_user_integral t where t.incomeType='0'");
			
			if(!StringUtils.isBlank(beginDate)) {
				sb.append(" and t.recordTime >= '").append(beginDate).append("'");
			}
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
			sb.append(" group by t.uid) t1 left join (select t.uid, sum(t.score) outputScore from tb_user_integral t where t.incomeType='1'");
			
			if(!StringUtils.isBlank(beginDate)) {
				sb.append(" and t.recordTime >= '").append(beginDate).append("'");
			}
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
			sb.append(" group by t.uid) t2 on t1.uid = t2.uid) t3, tb_user_info t4 where t3.uid = t4.uid");
			
			if(!StringUtils.isBlank(userName)) {
				userName = URLDecoder.decode(userName, "UTF-8");
				sb.append(" and t4.userName like '%").append(userName).append("%'");
			}
			if(!StringUtils.isBlank(uid)) {
				sb.append(" and t4.uid like '%").append(uid).append("%'");
			}
			if(!StringUtils.isBlank(telephone)) {
				sb.append(" and t4.telephone like '%").append(telephone).append("%'");
			}
			
			sb.append(" order by t3.incomeScore desc");
			
			SQLQuery sqlQuery = createSQLQuery(sb.toString());
			if(page != null) {
				sqlQuery.setFirstResult((page.getPageNo() - 1) * page.getPageSize());
				sqlQuery.setMaxResults(page.getPageSize());
			}
			
			sqlQuery.addScalar("uid", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userName", StandardBasicTypes.STRING);
			sqlQuery.addScalar("telephone", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userDept", StandardBasicTypes.STRING);
			sqlQuery.addScalar("city", StandardBasicTypes.STRING);
			sqlQuery.addScalar("postLevel", StandardBasicTypes.STRING);
			sqlQuery.addScalar("headImg", StandardBasicTypes.STRING);
			sqlQuery.addScalar("incomeScore", StandardBasicTypes.INTEGER);
			sqlQuery.addScalar("outputScore", StandardBasicTypes.INTEGER);
			
			List<Object[]> list = (List<Object[]>) sqlQuery.list();
			Object[] obj = null;
			
			if(list != null && list.size() > 0) {
				UserIntegral userIntegral = null;
				for(int i=0; i<list.size(); i++) {
					obj = list.get(i);
					
					userIntegral = new UserIntegral();
					userIntegral.setUid((String)obj[0]);
					userIntegral.setUserName((String)obj[1]);
					userIntegral.setTelephone((String)obj[2]);
					userIntegral.setUserDept((String)obj[3]);
					userIntegral.setCity((String)obj[4]);
					userIntegral.setPostLevel((String)obj[5]);
					userIntegral.setHeadImg((String)obj[6]);
					userIntegral.setIncomeScore((Integer)obj[7]);
					userIntegral.setOutputScore((Integer)obj[8]);
					userIntegral.setRestScore((Integer)obj[7] - (Integer)obj[8]);
					
					result.add(userIntegral);
				}
			}
		} catch(Exception e) {
			logger.error("--获取用户积分列表出现异常！", e);
		}
		return result;
	}
	
	/**
	 * 获取用户积分列表总数
	 */
	public Integer getUserIntegralCount(Page<UserIntegral> page, HttpServletRequest request) {
		try{
			String userName = request.getParameter("userName");
			String uid = request.getParameter("uid");
			String telephone = request.getParameter("telephone");
			String beginDate = request.getParameter("beginDate");
			String endDate = request.getParameter("endDate");
			if(!StringUtils.isBlank(endDate)) endDate = DateUtils.addDays(endDate, 1);
			
			StringBuffer sb = new StringBuffer();
			sb.append("select count(*) num from (select t1.uid, t1.incomeScore, ifnull(t2.outputScore, 0) outputScore from (select t.uid, sum(t.score) incomeScore from tb_user_integral t where t.incomeType='0'");
			
			if(!StringUtils.isBlank(beginDate)) {
				sb.append(" and t.recordTime >= '").append(beginDate).append("'");
			}
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
			sb.append(" group by t.uid) t1 left join (select t.uid, sum(t.score) outputScore from tb_user_integral t where t.incomeType='1'");
			
			if(!StringUtils.isBlank(beginDate)) {
				sb.append(" and t.recordTime >= '").append(beginDate).append("'");
			}
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
			sb.append(" group by t.uid) t2 on t1.uid = t2.uid) t3, tb_user_info t4 where t3.uid = t4.uid");
			
			if(!StringUtils.isBlank(userName)) {
				userName = URLDecoder.decode(userName, "UTF-8");
				sb.append(" and t4.userName like '%").append(userName).append("%'");
			}
			if(!StringUtils.isBlank(uid)) {
				sb.append(" and t4.uid like '%").append(uid).append("%'");
			}
			if(!StringUtils.isBlank(telephone)) {
				sb.append(" and t4.telephone like '%").append(telephone).append("%'");
			}
			
			SQLQuery sqlQuery = createSQLQuery(sb.toString());
			
			sqlQuery.addScalar("num",StandardBasicTypes.INTEGER );
			return ((Integer)(sqlQuery.uniqueResult())).intValue();
		} catch(Exception e) {
			logger.error("--获取用户积分列表总数出现异常！", e);
			return 0;
		}
	}
	
	public List<Object[]> addIntegralBetach(){
		try{
			StringBuffer sb = new StringBuffer();
			sb.append("select t2.uid sourceCode,t2.lcs_code uid,t2.true_name userName,t2.recordTime recordTime from (SELECT t1.UID,t1.LCS_CODE,t1.true_name,t1.recordTime FROM tc_user t1 where t1.lcs_code is not null) t2 where not EXISTS (SELECT t.uid, t.sourceCode FROM tb_user_integral t WHERE t2.uid=t.sourceCode and t2.LCS_CODE=t.uid and t.SOURCE='4')");
			
			SQLQuery sqlQuery = createSQLQuery(sb.toString());
			sqlQuery.addScalar("sourceCode", StandardBasicTypes.STRING);
			sqlQuery.addScalar("uid", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userName", StandardBasicTypes.STRING);
			sqlQuery.addScalar("recordTime", StandardBasicTypes.TIMESTAMP);
			
			List<Object[]> list = (List<Object[]>) sqlQuery.list();
			
			return list;
		} catch(Exception e) {
			logger.error("--获取用户积分列表总数出现异常！", e);
		}
		return null;
	}
	
	/**
	 * 获取用户积分列表
	 * @param request
	 * @param type 0获客，1关单
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> findUserIntegralListByType(HttpServletRequest request, String type) {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try{
			String userName = request.getParameter("userName");
			String uid = request.getParameter("uid");
			String telephone = request.getParameter("telephone");
			String beginDate = request.getParameter("beginDate");
			String endDate = request.getParameter("endDate");
			if(!StringUtils.isBlank(endDate)) endDate = DateUtils.addDays(endDate, 1);
			
			StringBuffer sb = new StringBuffer();
			sb.append("select t1.uid, t1.jxid, t1.userName, t1.telephone, t1.userDept, t1.postLevel, t2.score incomeScore from tb_user_info t1, (SELECT t.uid, sum(t.score) score FROM tb_user_integral t where t.incomeType='0'");
			
			if("0".equals(type)) {
				sb.append(" and t.source='4'");
			} else {
				sb.append(" and t.source!='4'");
			}
			
			if(!StringUtils.isBlank(beginDate)) {
				sb.append(" and t.recordTime >= '").append(beginDate).append("'");
			}
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
//			sb.append(" group by t.uid) t2 where t1.uid=t2.uid");
			sb.append(" group by t.uid) t2 where t1.uid=t2.uid and t1.userDept not like '%金信%'");
			
			if(!StringUtils.isBlank(userName)) {
				userName = URLDecoder.decode(userName, "UTF-8");
				sb.append(" and t1.userName like '%").append(userName).append("%'");
			}
			if(!StringUtils.isBlank(uid)) {
				sb.append(" and t1.uid like '%").append(uid).append("%'");
			}
			if(!StringUtils.isBlank(telephone)) {
				sb.append(" and t1.telephone like '%").append(telephone).append("%'");
			}
			
			sb.append(" order by t2.score desc");
			
			SQLQuery sqlQuery = createSQLQuery(sb.toString());
			
			sqlQuery.addScalar("uid", StandardBasicTypes.STRING);
			sqlQuery.addScalar("jxId", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userName", StandardBasicTypes.STRING);
			sqlQuery.addScalar("telephone", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userDept", StandardBasicTypes.STRING);
			sqlQuery.addScalar("postLevel", StandardBasicTypes.STRING);
			sqlQuery.addScalar("incomeScore", StandardBasicTypes.INTEGER);
			
			List<Object[]> list = (List<Object[]>) sqlQuery.list();
			Object[] obj = null;
			
			if(list != null && list.size() > 0) {
				Map<String, Object> userIntegral = null;
				for(int i=0; i<list.size(); i++) {
					obj = list.get(i);
					
					userIntegral = new HashMap<String, Object>();
					userIntegral.put("uid", obj[0]);
					userIntegral.put("jxId", obj[1]);
					userIntegral.put("userName", obj[2]);
					userIntegral.put("telephone", obj[3]);
					userIntegral.put("userDept", obj[4]);
					userIntegral.put("postLevel", obj[5]);
					userIntegral.put("incomeScore", obj[6]);
					
					result.add(userIntegral);
				}
			}
		} catch(Exception e) {
			logger.error("--获取用户获客列表出现异常！", e);
		}
		return result;
	}
	
	/**
	 * 获取用户剩余积分列表
	 */
	@SuppressWarnings("unchecked")
	public List<UserIntegral> findUserRestIntegralList(HttpServletRequest request) {
		List<UserIntegral> result = new ArrayList<UserIntegral>();
		try{
			String endDate = request.getParameter("endDate");
			if(!StringUtils.isBlank(endDate)) endDate = DateUtils.addDays(endDate, 1);
			
			StringBuffer sb = new StringBuffer();
			sb.append("select t4.uid, t4.userName, t4.telephone, t4.userDept, t4.city, t4.postLevel, t4.headImg, t3.incomeScore, t3.outputScore from (select t1.uid, t1.incomeScore, ifnull(t2.outputScore, 0) outputScore from (select t.uid, sum(t.score) incomeScore from tb_user_integral t where t.incomeType='0' and source='4'");
			
			if(!StringUtils.isBlank(endDate)) {
				sb.append(" and t.recordTime < '").append(endDate).append("'");
			}
			
			sb.append(" group by t.uid) t1 left join (select t.uid, sum(t.score) outputScore from tb_user_integral t where t.incomeType='1'");
			
			sb.append(" group by t.uid) t2 on t1.uid = t2.uid) t3, tb_user_info t4 where t3.uid = t4.uid");
			
			sb.append(" order by t3.incomeScore desc");
			
			SQLQuery sqlQuery = createSQLQuery(sb.toString());
			
			sqlQuery.addScalar("uid", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userName", StandardBasicTypes.STRING);
			sqlQuery.addScalar("telephone", StandardBasicTypes.STRING);
			sqlQuery.addScalar("userDept", StandardBasicTypes.STRING);
			sqlQuery.addScalar("city", StandardBasicTypes.STRING);
			sqlQuery.addScalar("postLevel", StandardBasicTypes.STRING);
			sqlQuery.addScalar("headImg", StandardBasicTypes.STRING);
			sqlQuery.addScalar("incomeScore", StandardBasicTypes.INTEGER);
			sqlQuery.addScalar("outputScore", StandardBasicTypes.INTEGER);
			
			List<Object[]> list = (List<Object[]>) sqlQuery.list();
			Object[] obj = null;
			
			if(list != null && list.size() > 0) {
				UserIntegral userIntegral = null;
				for(int i=0; i<list.size(); i++) {
					obj = list.get(i);
					
					userIntegral = new UserIntegral();
					userIntegral.setUid((String)obj[0]);
					userIntegral.setUserName((String)obj[1]);
					userIntegral.setTelephone((String)obj[2]);
					userIntegral.setUserDept((String)obj[3]);
					userIntegral.setCity((String)obj[4]);
					userIntegral.setPostLevel((String)obj[5]);
					userIntegral.setHeadImg((String)obj[6]);
					userIntegral.setIncomeScore((Integer)obj[7]);
					userIntegral.setOutputScore((Integer)obj[8]);
					userIntegral.setRestScore((Integer)obj[7] - (Integer)obj[8]);
					
					result.add(userIntegral);
				}
			}
		} catch(Exception e) {
			logger.error("--获取用户剩余积分列表出现异常！", e);
		}
		return result;
	}
}
